-- @owner: wang_zhengyuan
-- @date: 2022-02-19
-- @testpoint: 多表inner join,orderby后索引结合where后面的过滤索引

--step1:建表;expect:成功
drop table if exists t_ustore_dql_cbo_index_019;
create table t_ustore_dql_cbo_index_019(
     col_1 integer,
     col_2 bigint,
     col_3 double precision,
     col_4 decimal(12,6),
     col_5 bool,
     col_6 char(30),
     col_7 varchar2(50),
     col_8 varchar(30),
     col_9 interval day to second,
     col_10 timestamp,
     col_11 date,
     col_12 date,
     col_13 timestamp without time zone,
     col_14 blob,
     col_15 clob,
     col_16 int[]
) with (storage_type=ustore);

--step2:创建序列;expect:成功
drop sequence if exists s_ustore_dql_cbo_index_019;
create sequence s_ustore_dql_cbo_index_019
    increment by 1 start with 10;

--step3:创建索引;expect:成功
drop index if exists idx_unique_opengauss_function_ustore_cbo_index_case_019;
create unique index idx_unique_opengauss_function_ustore_cbo_index_case_019
    on t_ustore_dql_cbo_index_019(col_1);
drop index if exists idx_opengauss_function_ustore_cbo_index_case_019_002;
create index idx_opengauss_function_ustore_cbo_index_case_019_002
    on t_ustore_dql_cbo_index_019(col_2);

--step4:插入数据;expect:成功
insert into t_ustore_dql_cbo_index_019
    values(1,s_ustore_dql_cbo_index_019.nextval,   1+445.255,98*0.99,  true,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','6','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),to_date('2018-11-03 14:14:12'),to_date('2019-01-03 14:14:12'),to_date('2019-01-03 14:14:12'),'10010101001010',rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_dql_cbo_index_019
    values(2,s_ustore_dql_cbo_index_019.nextval,   1+445.255,98*0.99, false,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','5','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),to_date('2018-11-03 14:14:12'),to_date('2019-01-03 14:14:12'),to_date('2019-01-03 14:14:12'),'10010101001010',rpad('abc','9','a@123&^%djgk'),'{43,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_dql_cbo_index_019
    values(3,s_ustore_dql_cbo_index_019.nextval,   1+445.255,98*0.99, false,lpad('abc','3','@'),lpad('abc','3','b'),rpad('abc','3','e'),(interval '4 5:07:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),to_date('2018-11-03 14:14:12'),to_date('2019-01-03 14:14:12'),to_date('2019-01-03 14:14:12'),'101010101001010',rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_dql_cbo_index_019
    values(4,s_ustore_dql_cbo_index_019.nextval,   1+445.255,98*0.99,  true,lpad('abc','3','@'),lpad('abc','4','b'),rpad('abc','6','e'),(interval '4 5:07:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'), to_date('2018-11-03 14:14:12'),to_date('2019-01-03 14:14:12'),to_date('2019-01-03 14:14:12'),'1010101010101010',rpad('abc','9','a@123&^%djgk'),'{43,535,5645645,6767,76,67,56,48,979,978,7}');

begin
for i in 5 .. 10000 loop
insert into t_ustore_dql_cbo_index_019
    values(i,'1'||i,446.255,98*0.99,true,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','6','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'), to_date('2018-11-03 14:14:12'),to_date('2019-01-03 14:14:12'),to_date('2019-01-03 14:14:12'),'10001101001010',rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
end loop;
end;
/

--step5:创建表;expect:成功
analyze  t_ustore_dql_cbo_index_019 ;
drop table if exists t_ustore_dql_cbo_index_020;
create table t_ustore_dql_cbo_index_020(
     col_1 integer,
     col_2 bigint,
     col_3 double precision,
     col_4 decimal(12,6),
     col_5 bool,
     col_6 char(30),
     col_7 varchar2(50),
     col_8 varchar(30),
     col_9 interval day to second,
     col_10 timestamp,
     col_11 date,
     col_12 date,
     col_13 timestamp without time zone,
     col_14 blob,
     col_15 clob,
     col_16 int[]
) with (storage_type=ustore);

--step6:创建序列;expect:成功
drop sequence if exists s_ustore_dql_cbo_index_020;
create sequence s_ustore_dql_cbo_index_020
    increment by 1 start with 10;

--step7:创建索引;expect:成功
drop index if exists idx_unique_opengauss_function_ustore_cbo_index_case_020;
create unique index idx_unique_opengauss_function_ustore_cbo_index_case_020
    on t_ustore_dql_cbo_index_020(col_1);
drop index if exists idx_opengauss_function_ustore_cbo_index_case_020;
create index idx_opengauss_function_ustore_cbo_index_case_020
    on t_ustore_dql_cbo_index_020(col_2);

--step8:插入数据;expect:成功
truncate table t_ustore_dql_cbo_index_020;
insert into t_ustore_dql_cbo_index_020
    values(1,s_ustore_dql_cbo_index_020.nextval,   1+445.255,98*0.99,  true,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','6','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),    to_date('2018-11-03 14:14:12'),to_date('2019-01-03 14:14:12'),to_date('2019-01-03 14:14:12'),'10010101001010',rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_dql_cbo_index_020
    values(2,s_ustore_dql_cbo_index_020.nextval,   1+445.255,98*0.99, false,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','5','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),    to_date('2018-11-03 14:14:12'),to_date('2019-01-03 14:14:12'),to_date('2019-01-03 14:14:12'),'10010101001010',rpad('abc','9','a@123&^%djgk'),'{43,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_dql_cbo_index_020
    values(3,s_ustore_dql_cbo_index_020.nextval,   1+445.255,98*0.99, false,lpad('abc','3','@'),lpad('abc','3','b'),rpad('abc','3','e'),(interval '4 5:07:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),    to_date('2018-11-03 14:14:12'),to_date('2019-01-03 14:14:12'),to_date('2019-01-03 14:14:12'),'10010101001010',rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_dql_cbo_index_020
    values(4,s_ustore_dql_cbo_index_020.nextval,   1+445.255,98*0.99,  true,lpad('abc','3','@'),lpad('abc','4','b'),rpad('abc','6','e'),(interval '4 5:07:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),    to_date('2018-11-03 14:14:12'),to_date('2019-01-03 14:14:12'),to_date('2019-01-03 14:14:12'),'10010101001010',rpad('abc','9','a@123&^%djgk'),'{43,535,5645645,6767,76,67,56,48,979,978,7}');

begin
for i in 5 .. 30 loop
insert into t_ustore_dql_cbo_index_020 values(i,'1'||i,446.255,98*0.99,true,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','6','e'),(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'), to_date('2018-11-03 14:14:12'),to_date('2019-01-03 14:14:12'),to_date('2019-01-03 14:14:12'),'10010101001010',rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
end loop;
end;
/

--step9:多表inner join,orderby后索引结合where后面的过滤索引;expect:成功
analyze  t_ustore_dql_cbo_index_020 ;
select a.col_1,b.col_1 from t_ustore_dql_cbo_index_019 a
    inner join t_ustore_dql_cbo_index_020 b
        on a.col_2=b.col_2
    where a.col_2 >10 order by a.col_1;
